SherbyNet - /page/wikibook/entry.mu

Micron Document

<!DOCTYPE html>

<title>User-defined function</title>

</head>

User-defined function

</h1>

</header>

A user-defined function (UDF) is a <a href="Function_(programming)" class="mw-redirect" title="Function (programming)">function</a> provided by the user of a program or environment, in a context where the usual assumption is that functions are built into the program or environment. UDFs are usually written for the requirement of its creator.

<div class="mw-heading mw-heading2"><h2 id="BASIC_language">BASIC language</h2></div>

In some old implementations of the <a href="BASIC" title="BASIC">BASIC</a> programming language, user-defined functions are defined using the "DEF FN" syntax. More modern dialects of BASIC are influenced by the <a href="Structured_programming" title="Structured programming">structured programming</a> paradigm, where most or all of the code is written as user-defined functions or procedures, and the concept becomes practically redundant.

<div class="mw-heading mw-heading2"><h2 id="COBOL_language">COBOL language</h2></div>

In the <a href="COBOL" title="COBOL">COBOL</a> programming language, a user-defined function is an entity that is defined by the user by specifying a FUNCTION-ID paragraph. A user-defined function must return a value by specifying the RETURNING phrase of the procedure division header and they are invoked using the function-identifier syntax. See the ISO/IEC 1989:2014 Programming Language COBOL standard for details.

As of May 2022, the IBM Enterprise COBOL for <a href="Z/OS" title="Z/OS">z/OS</a> 6.4 (<a href="IBM_COBOL" title="IBM COBOL">IBM COBOL</a>) compiler contains support for user-defined functions.

<div class="mw-heading mw-heading2"><h2 id="Databases">Databases</h2></div>

In <a href="Relational_database_management_system" class="mw-redirect" title="Relational database management system">relational database management systems</a>, a user-defined function provides a mechanism for extending the functionality of the <a href="Database_server" title="Database server">database server</a> by adding a function, that can be evaluated in standard <a href="Query_language" title="Query language">query language</a> (usually <a href="SQL" title="SQL">SQL</a>) statements. The <a href="ISO/IEC_9075" title="ISO/IEC 9075">SQL standard</a> distinguishes between <a href="Scalar_(computing)" class="mw-redirect" title="Scalar (computing)">scalar</a> and table functions. A scalar function returns only a single value (or <a href="Null_(SQL)" title="Null (SQL)">NULL</a>), whereas a table function returns a (relational) table comprising zero or more rows, each row with one or more columns.

User-defined functions in SQL are declared using the <code>CREATE FUNCTION</code> statement. For example, a user-defined function that converts Celsius to Fahrenheit (a temperature scale used in USA) might be declared like this:

<div class="mw-highlight mw-highlight-lang-sql mw-content-ltr" dir="ltr"><pre>CREATE FUNCTION dbo.CtoF(Celsius FLOAT)

RETURNS FLOAT

RETURN (Celsius * 1.8) + 32

</pre></div>

Once created, a user-defined function may be used in <a href="Expression_(programming)" class="mw-redirect" title="Expression (programming)">expressions</a> in SQL statements. For example, it can be invoked where most other intrinsic functions are allowed. This also includes <a href="Select_(SQL)" title="Select (SQL)">SELECT statements</a>, where the function can be used against data stored in tables in the database. Conceptually, the function is evaluated once per row in such usage. For example, assume a table named <code class="mw-highlight mw-highlight-lang-text mw-content-ltr" style="" dir="ltr">Elements</code>, with a row for each known chemical element. The table has a column named BoilingPoint for the boiling point of that element, in Celsius. The query

<div class="mw-highlight mw-highlight-lang-sql mw-content-ltr" dir="ltr"><pre>SELECT Name, CtoF(BoilingPoint)

FROM Elements

</pre></div>

would retrieve the name and the boiling point from each row. It invokes the <code>CtoF</code> user-defined function as declared above in order to convert the value in the column to a value in Fahrenheit.

Each user-defined function carries certain properties or characteristics. The SQL standard defines the following properties:

<ul><li>Language - defines the programming language in which the user-defined function is implemented; examples include SQL, C, C# and Java.</li>

<li>Parameter style - defines the conventions that are used to pass the function parameters and results between the implementation of the function and the database system (only applicable if language is not SQL).</li>

<li>Specific name - a name for the function that is unique within the database. Note that the function name does not have to be unique, considering <a href="Overloaded_function" class="mw-redirect" title="Overloaded function">overloaded functions</a>. Some SQL implementations require that function names are unique within a database, and overloaded functions are not allowed.</li>

<li>Determinism - specifies whether the function is deterministic or not. The determinism characteristic has an influence on the <a href="Query_optimizer" class="mw-redirect" title="Query optimizer">query optimizer</a> when compiling a SQL statement.</li>

<li>SQL-data access - tells the database management system whether the function contains no SQL statements (NO SQL), contains SQL statements but does not access any tables or <a href="View_(SQL)" title="View (SQL)">views</a> (CONTAINS SQL), reads data from tables or views (READS SQL DATA), or actually modifies data in the database (MODIFIES SQL DATA).</li></ul>

User-defined functions should not be confused with <a href="Stored_procedure" title="Stored procedure">stored procedures</a>. Stored procedures allow the user to group a set of SQL commands. A procedure can accept parameters and execute its SQL statements depending on those parameters. A procedure is not an expression and, thus, cannot be used like user-defined functions.

Some database management systems allow the creation of user defined functions in languages other than SQL. <a href="Microsoft_SQL_Server" title="Microsoft SQL Server">Microsoft SQL Server</a>, for example, allows the user to use <a href="List_of_CLI_languages" title="List of CLI languages">.NET languages</a> including C# for this purpose. DB2 and Oracle support user-defined functions written in C or Java programming languages.

<div class="mw-heading mw-heading3"><h3 id="SQL_Server_2000">SQL Server 2000</h3></div>

There are three types of UDF in <a href="Microsoft_SQL_Server" title="Microsoft SQL Server">Microsoft SQL Server</a> 2000: <a href="Scalar_function" class="mw-redirect" title="Scalar function">scalar functions</a>, inline table-valued functions, and multistatement table-valued functions.

Scalar functions return a single data value (not a table) with RETURNS clause. Scalar functions can use all scalar data types, with exception of timestamp and user-defined data types.

Inline table-valued functions return the <a href="Result_set" title="Result set">result set</a> of a single SELECT statement.

Multistatement table-valued functions return a table, which was built with many TRANSACT-SQL statements.

User-defined functions can be invoked from a query like built‑in functions such as OBJECT_ID, LEN, DATEDIFF, or can be executed through an EXECUTE statement like stored procedures.

Performance Notes:

1. On Microsoft SQL Server 2000 a table-valued function which "wraps" a View may be much faster than the View itself. The following MyFunction is an example of a "function-wrapper" which runs faster than the underlying view MyView:

<div class="mw-highlight mw-highlight-lang-tsql mw-content-ltr" dir="ltr"><pre>CREATE FUNCTION MyFunction()

RETURNS @Tbl TABLE

(

StudentID VARCHAR(255),

SAS_StudentInstancesID INT,

Label VARCHAR(255),

Value MONEY,

CMN_PersonsID INT

)

AS

BEGIN

INSERT @Tbl

(

StudentID,

SAS_StudentInstancesID,

Label,

Value,

CMN_PersonsID

)

SELECT

StudentID,

SAS_StudentInstancesID,

Label,

Value,

CMN_PersonsID

FROM MyView -- where MyView selects (with joins) the same columns from large table(s)

RETURN

END

</pre></div>

2. On Microsoft SQL Server 2005 the result of the same code execution is the opposite: view is executed faster than the "function-wrapper".

User-defined functions are subroutines made of one or more Transact-SQL statements that can be used to encapsulate code for reuse.

It takes zero or more arguments and evaluates a return value. Has both control-flow and DML statements in its body similar to stored procedures.

Does not allow changes to any Global Session State, like modifications to database or external resource, such as a file or network.

Does not support output parameter.

DEFAULT keyword must be specified to pass the default value of parameter.

Errors in UDF cause UDF to abort which, in turn, aborts the statement that invoked the UDF.

-- Input dimensions in centimeters

(

@CubeLength decimal(4,1),

@CubeWidth decimal(4,1),

@CubeHeight decimal(4,1)

)

RETURNS decimal(12,3)

AS

BEGIN

RETURN(@CubeLength * @CubeWidth * @CubeHeight)

END

</pre></div>

Data type supported in Microsoft SQL Server 2000

Like a temporary table used to store results

Mostly used to define <a href="Temporary_variable" title="Temporary variable">temporary variable</a> of type (table) and the return value of a UDF

The scope is limited to function, stored procedure, or batch in which it is defined

Assignment operation is not allowed between (Table) variables

May be used in SELECT, INSERT, UPDATE, and DELETE

CREATE FUNCTION to create UDF

ALTER FUNCTION to change the characteristics of UDF

DROP FUNCTION to remove UDF

<div class="mw-heading mw-heading3"><h3 id="Apache_Hive">Apache Hive</h3></div>

<a href="Apache_Hive" title="Apache Hive">Apache Hive</a> defines, in addition to the regular user-defined functions (UDF), also user-defined aggregate functions (UDAF) and table-generating functions (UDTF).<a href="#cite_note-1">[1]</a> Hive enables developers to create their own custom functions with Java.<a href="#cite_note-2">[2]</a>

<div class="mw-heading mw-heading3"><h3 id="Apache_Doris">Apache Doris</h3></div>

Apache Doris, an open-source real-time analytical database, allows external users to contribute their own UDFs written in C++ to it.<a href="#cite_note-3">[3]</a>

<div class="mw-heading mw-heading2"><h2 id="References">References</h2></div>

/* start https://en.wikipedia.org/ */

.mw-parser-output .reflist{margin-bottom:0.5em;list-style-type:decimal}@media screen{.mw-parser-output .reflist{font-size:90%}}.mw-parser-output .reflist .references{font-size:100%;margin-bottom:0;list-style-type:inherit}.mw-parser-output .reflist-columns-2{column-width:30em}.mw-parser-output .reflist-columns-3{column-width:25em}.mw-parser-output .reflist-columns{margin-top:0.3em}.mw-parser-output .reflist-columns ol{margin-top:0}.mw-parser-output .reflist-columns li{page-break-inside:avoid;break-inside:avoid-column}.mw-parser-output .reflist-upper-alpha{list-style-type:upper-alpha}.mw-parser-output .reflist-upper-roman{list-style-type:upper-roman}.mw-parser-output .reflist-lower-alpha{list-style-type:lower-alpha}.mw-parser-output .reflist-lower-greek{list-style-type:lower-greek}.mw-parser-output .reflist-lower-roman{list-style-type:lower-roman}

/* end https://en.wikipedia.org/ */

</style><div class="reflist">

/* start https://en.wikipedia.org/ */

.mw-parser-output cite.citation{font-style:inherit;word-wrap:break-word}.mw-parser-output .citation q{quotes:"\"""\"""'""'"}.mw-parser-output .citation:target{background-color:rgba(0,127,255,0.133)}.mw-parser-output .id-lock-free.id-lock-free a{background:url("./mw/Lock-green.svg")right 0.1em center/9px no-repeat}.mw-parser-output .id-lock-limited.id-lock-limited a,.mw-parser-output .id-lock-registration.id-lock-registration a{background:url("./mw/Lock-gray-alt-2.svg")right 0.1em center/9px no-repeat}.mw-parser-output .id-lock-subscription.id-lock-subscription a{background:url("./mw/Lock-red-alt-2.svg")right 0.1em center/9px no-repeat}.mw-parser-output .cs1-ws-icon a{background:url("./mw/Wikisource-logo.svg")right 0.1em center/12px no-repeat}body:not(.skin-timeless):not(.skin-minerva) .mw-parser-output .id-lock-free a,body:not(.skin-timeless):not(.skin-minerva) .mw-parser-output .id-lock-limited a,body:not(.skin-timeless):not(.skin-minerva) .mw-parser-output .id-lock-registration a,body:not(.skin-timeless):not(.skin-minerva) .mw-parser-output .id-lock-subscription a,body:not(.skin-timeless):not(.skin-minerva) .mw-parser-output .cs1-ws-icon a{background-size:contain;padding:0 1em 0 0}.mw-parser-output .cs1-code{color:inherit;background:inherit;border:none;padding:inherit}.mw-parser-output .cs1-hidden-error{display:none;color:var(--color-error,#d33)}.mw-parser-output .cs1-visible-error{color:var(--color-error,#d33)}.mw-parser-output .cs1-maint{display:none;color:#085;margin-left:0.3em}.mw-parser-output .cs1-kern-left{padding-left:0.2em}.mw-parser-output .cs1-kern-right{padding-right:0.2em}.mw-parser-output .citation .mw-selflink{font-weight:inherit}@media screen{.mw-parser-output .cs1-format{font-size:95%}html.skin-theme-clientpref-night .mw-parser-output .cs1-maint{color:#18911f}}@media screen and (prefers-color-scheme:dark){html.skin-theme-clientpref-os .mw-parser-output .cs1-maint{color:#18911f}}

/* end https://en.wikipedia.org/ */

</style><cite class="citation web cs1"><a rel="nofollow" class="external text" href="https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF">"LanguageManual UDF - Apache Hive - Apache Software Foundation"</a>. 26 June 2015.</cite>

</li>

<cite class="citation web cs1"><a rel="nofollow" class="external text" href="https://cwiki.apache.org/confluence/display/Hive/HivePlugins">"HivePlugins - Apache Hive - Apache Software Foundation"</a>. 26 June 2015.</cite>

</li>

<li id="cite_note-3"><a href="#cite_ref-3">^</a> <cite class="citation web cs1"><a rel="nofollow" class="external text" href="https://doris.apache.org/docs/dev/ecosystem/udf/contribute-udf?_highlight=udf">"Apache Doris UDF"</a>. Retrieved 8 April 2023.</cite>

</li>

</ol></div></div>

<div class="mw-heading mw-heading2"><h2 id="External_links">External links</h2></div>

<ul><li><a rel="nofollow" class="external text" href="http://msdn2.microsoft.com/en-us/library/aa258261(SQL.80).aspx">Microsoft SQL Server reference for CREATE FUNCTION</a></li>

<li><a rel="nofollow" class="external text" href="http://dev.mysql.com/doc/refman/5.5/en/adding-functions.html">MySQL manual section on UDFs</a></li>

<li><a rel="nofollow" class="external text" href="http://pic.dhe.ibm.com/infocenter/db2luw/v10r5/index.jsp?topic=%2Fcom.ibm.db2.luw.sql.ref.doc%2Fdoc%2Fr0000917.html">DB2 CREATE FUNCTION statement</a></li></ul></div><div><div class="zim-footer">

This article is issued from <a class="external text" title="Last edited on 2025-06-23" href="https://en.wikipedia.org/wiki/?title=User-defined_function&oldid=1296965645">Wikipedia</a>. The text is available under <a class="external text" href="https://creativecommons.org/licenses/by-sa/4.0/deed.en">Creative Commons Attribution-Share Alike 4.0</a> unless otherwise noted. Additional terms may apply for the media files.

</div>

</div></div>

</div>

</main>

</div>

</body></html>